package com.futvan.z.framework.util;

import java.awt.Desktop;
import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.InputStream;
import java.util.ArrayList;

import jxl.Cell;
import jxl.CellView;
import jxl.Sheet;
import jxl.SheetSettings;
import jxl.Workbook;
import jxl.format.Alignment;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.format.VerticalAlignment;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;

/**
 * Excel操作工具类
 * @author 42239
 *
 */
public class ExcelUtil {
	public static void main(String[] args) throws Exception{
	}
	/**
	 * 向Excel文件插入数据
	 * @param File 
	 * @param sheetNum 工作表序号
	 * @param y 行
	 * @param x 列
	 * @param value 内容
	 * @throws Exception 
	 */
	public static void setExcelValue(String File,int sheetNum,int x,int y,String value) throws Exception{
		File file = new File(File);
		//如果找到相同的文件，执行删除
		if(!file.exists() && !file.isFile()){
			return;
		}
		//Excel获得文件 
		Workbook wb = Workbook.getWorkbook(new File(File)); 
		//打开一个文件的副本，并且指定数据写回到原文件 
		WritableWorkbook book = Workbook.createWorkbook(new File(File),wb); 
		//获取工作表 
		WritableSheet sheet=book.getSheet(sheetNum);
		sheet.addCell(new Label(y,x,value)); 
		book.write(); 
		book.close(); 
	}
	/**
	 * 读取Excel文件
	 * @param File 文件名
	 * @param sheetNum 工作表序号
	 * @param y 行
	 * @param x 列
	 * @throws Exception 
	 */
	public static String getExcelValue(String File,int sheetNum,int y,int x) throws Exception{
		String result = "";
		File file = new File(File);
		//如果找到相同的文件，执行删除
		if(file.exists() && file.isFile()){
			Workbook book= Workbook.getWorkbook(new File(File)); 
			//获得第一个工作表对象 
			Sheet sheet=book.getSheet(sheetNum); 
			//得到第一列第一行的单元格 
			Cell cell1=sheet.getCell(x,y); 
			result=cell1.getContents().toString(); 
			book.close();
		}
		return result;
	}

	/**
	 * 读取Excel文件
	 * @param File 文件路径
	 * @return List集合
	 * 
	 *  返回遍历
	        String FilePath = UtilClass.OpenFilesSelect();
    		if(!"".equals(FilePath)){
    			ArrayList list = UtilClass.getExcelValue(FilePath); 
    			if(list.size()>0){ 
    				for (Iterator iter = list.iterator(); iter.hasNext();) { 
    					com.futvan.util.function.SystemUtil.Log((String)iter.next()); 
    				} 
    			}
    		}
	 * @throws Exception 
	 * @throws BiffException 
	 */
	public static ArrayList getExcelValue(String File) throws Exception{
		ArrayList returnvalue = new ArrayList();
		Workbook book= Workbook.getWorkbook(new File(File)); 
		Sheet sheet=book.getSheet(0);
		for(int i=1;i<sheet.getRows();i++){
			for(int j=0;j<sheet.getColumns();j++){
				returnvalue.add(sheet.getCell(j,i).getContents());
			}
		}
		return returnvalue;
	}
}
